﻿Imports System.Data

Module modTC

    '2007/04/12,sammi.彙整.
    'ReviseDate:2003/08/07  '修改處理AdditionalXML Function
    'ReviseDate:2003/08/06  'ExecuteSQLNoneQuery_UPD加上處理參數的SQL Statement
    'ReviseDate:2002/12/12  'Function都加上Try Catch,當發生錯誤時以取得發生錯誤的Function Name

    Public strSQL As String

    Public Const defInteger As Integer = -999   '宣告整數使用的常數，用以辨識參數是否有傳入Function中。
    Public Const defString As String = "Null"   '宣告字串使用的常數，用以辨識參數是否有傳入Function中。
    Public Const defDateTime As Date = #12/31/1900#  '宣告日期使用的常數，用以辨識參數是否有傳入Function中。
    Public Const defTCErrCode As String = "0000-699999"
    Public Const defNullDate As Date = #12/31/1910#  '宣告日期使用的常數，用以辨識參數是否要變更為Null。
    Public Const defNullInteger As Integer = -9999   '宣告數值使用的常數，用以辨識參數是否要變更為Null。

#Region "--- Connection,DataBaseType ---"

    Public Function GetConnectionString(ByRef strDataBaseType As String) As String

        Dim objLic As New iMESLicxManager.clsDBCom

        Try
            '從Web.config取出目前的Connection string
            GetConnectionString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")

            '若DataBase是Oracle或SQL才需要加上Password
            If strDataBaseType <> "msaccess" Then
                '加上Password()
                GetConnectionString = GetConnectionString & ";Password=" & objLic.GetDBPassword
            End If
        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetConnectionString: " & e1.Message)
        Finally
            objLic = Nothing
        End Try

    End Function

    Public Function GetReportConnectionString(ByRef strReportDataBaseType As String) As String

        Dim objLic As New iMESLicxManager.clsDBCom

        Try
            '從Web.config取出目前的Connection string
            GetReportConnectionString = System.Configuration.ConfigurationSettings.AppSettings.Get("ReportConnectionString")

            '若DataBase是Oracle或SQL才需要加上Password
            If strReportDataBaseType <> "msaccess" Then
                '加上Password()
                GetReportConnectionString = GetReportConnectionString & ";Password=" & objLic.GetDBPassword
            End If
        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetConnectionString: " & e1.Message)
        Finally
            objLic = Nothing
        End Try

    End Function

    Public Function GetDataBaseType() As String

        Try
            '從Web.config取出目前的DatabaseType
            GetDataBaseType = System.Configuration.ConfigurationSettings.AppSettings.Get("DatabaseType")
            'GetDataBaseType = "oracle"

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetDataBaseType: " & e1.Message)

        End Try

    End Function

    Public Function GetReportDataBaseType() As String

        Try
            '從Web.config取出目前的DatabaseType
            GetReportDataBaseType = System.Configuration.ConfigurationSettings.AppSettings.Get("ReportDatabaseType")
            'GetDataBaseType = "oracle"

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetDataBaseType: " & e1.Message)

        End Try

    End Function

    Public Function GetMQType() As String

        Try
            '從Web.config取出目前的MessageQueueType
            GetMQType = System.Configuration.ConfigurationSettings.AppSettings.Get("MessageQueueType")

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetMQType: " & e1.Message)

        End Try

    End Function

    Public Function CreateConnection(ByRef strConnectionString As String) As OleDb.OleDbConnection

        Dim cnnKernal As OleDb.OleDbConnection

        Try
            '//建立連線
            cnnKernal = New OleDb.OleDbConnection
            cnnKernal.ConnectionString = strConnectionString
            cnnKernal.Open()

            CreateConnection = cnnKernal

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.CreateConnection: " & e1.Message)

        End Try

    End Function

    Public Function CloseConnection(ByRef cnnKernal As OleDb.OleDbConnection) As String

        Try
            If Not cnnKernal Is Nothing Then
                cnnKernal.Close()
            End If

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.CloseConnection: " & e1.Message)

        End Try

    End Function

    Public Function CreateODBCConnection(ByVal strConnectionString As String) As Odbc.OdbcConnection

        Dim cnnKernal As Odbc.OdbcConnection

        Try
            '//建立連線
            cnnKernal = New Odbc.OdbcConnection
            cnnKernal.ConnectionString = strConnectionString
            cnnKernal.Open()

            CreateODBCConnection = cnnKernal

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.CreateODBCConnection: " & e1.Message)

        End Try

    End Function

    Public Function CloseODBCConnection(ByRef cnnKernal As Odbc.OdbcConnection) As String

        Try
            If Not cnnKernal Is Nothing Then
                cnnKernal.Close()
            End If

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.CloseODBCConnection: " & e1.Message)

        End Try

    End Function


#End Region

#Region "--- ERP Connection ---"

    Public Function GetERPConnectionString(ByRef strERPDataBaseType As String) As String
        Dim objLic As New iMESLicxManager.clsDBCom

        Try
            '從Web.config取出目前的Connection string
            GetERPConnectionString = System.Configuration.ConfigurationSettings.AppSettings.Get("ERPConnectionString")

            '若DataBase是Oracle或SQL才需要加上Password
            If strERPDataBaseType <> "msaccess" Then
                '加上Password()
                GetERPConnectionString = GetERPConnectionString & ";Password=" & objLic.GetDBPassword
            End If
        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetERPConnectionString: " & e1.Message)
        Finally
            objLic = Nothing
        End Try

    End Function

    Public Function GetERPDataBaseType() As String

        Try
            '從Web.config取出目前的DatabaseType
            GetERPDataBaseType = System.Configuration.ConfigurationSettings.AppSettings.Get("ERPDatabaseType")
            'GetDataBaseType = "oracle"

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.GetERPDataBaseType: " & e1.Message)

        End Try

    End Function


#End Region

#Region "--- Execute ---"

    Public Function ExecuteSQLNoneQuery(ByRef strDatabaseType As String, ByRef strConnectionString As String, ByRef strCommandText As String) As String

        '//執行DML的SQL Statement
        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)

            '//Create connection
            cnnKernal = CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            '//Execute
            cmmKernal.CommandText = strCommandText
            cmmKernal.ExecuteNonQuery()

            '//TransactionLog
            '2011/12/16 Aaron, 寫入交易紀錄
            If blnTransactionLog = True Then
                intSQLID += 1
                cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                cmmKernal.ExecuteNonQuery()
            End If

            ''Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw 'New Exception("modTC.ExecuteSQLNoneQuery: " & e1.Message & Chr(13) & strCommandText)

        Finally
            CloseConnection(cnnKernal)
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not oTran Is Nothing Then oTran = Nothing

        End Try

    End Function

    Public Function ExecuteSQLNoneQuery_UPD(ByRef strDatabaseType As String, ByRef strConnectionString As String, ByRef CollectionSQL As Collection) As String
        '//執行DML的SQL Statement

        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction
        Dim MaxIndex As Integer
        Dim CurIndex As Integer
        Dim strCommandText As String

        Dim arrPar As Array

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//Create connection
            cnnKernal = CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            Dim pc As OleDb.OleDbParameterCollection = cmmKernal.Parameters
            Dim Index_i As Integer

            '//Execute
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            For Each strCommandText In CollectionSQL
                If InStr(strCommandText, "||") <> 0 Then    '此 SQL 為Parameter Type
                    arrPar = Split(strCommandText, "||")
                    strCommandText = SQLCommandTuning(strDatabaseType, arrPar(0))


                    For Index_i = 1 To arrPar.Length - 1
                        pc.Add("NodeXMLString_NEW" & Index_i, System.Data.OleDb.OleDbType.LongVarChar)
                        pc("NodeXMLString_NEW" & Index_i).Value = arrPar(Index_i)
                    Next Index_i

                    cmmKernal.CommandText = strCommandText
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                Else
                    strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                    cmmKernal.CommandText = strCommandText
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                End If

            Next strCommandText

            'Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Dim strErrorCode As String = Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1)
                If strErrorCode = "ORA-12899" Then '值太大,只適用在10G.
                    Dim aryMsg() As String
                    aryMsg = Split(ex.Message, ".")
                    If aryMsg.Length > 2 Then
                        '擷取到ColumnName,加上[%ColumnName%],前端即可做語系轉換.
                        Throw New iMESException.MESException(strErrorCode, Replace(Replace(aryMsg(2), """", "[%", 1, 1, CompareMethod.Text), """", "%]", 1, 1, CompareMethod.Text) & ControlChars.CrLf & ex.Message)
                    Else
                        Throw New iMESException.MESException(strErrorCode, ex.Message)
                    End If
                Else
                    Throw New iMESException.MESException(strErrorCode, ex.Message)
                End If
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.ExecuteSQLNoneQuery_UPD: " & e1.Message & Chr(13) & strCommandText)

        Finally
            CloseConnection(cnnKernal)
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not oTran Is Nothing Then oTran = Nothing

        End Try


    End Function

    Public Function ExecuteSQLNoneQuery_Upd2(ByRef strDatabaseType As String, ByRef strConnectionString As String, ByRef dsUpdate As DataSet) As String

        'Batch執行DML SQL Statement
        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction
        Dim i As Integer
        Dim intRows As Integer
        Dim strXMLValue As String
        Dim strTableName As String

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//Create connection
            cnnKernal = CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            strTableName = dsUpdate.Tables(0).TableName
            intRows = dsUpdate.Tables(0).Rows.Count - 1

            '//Execute
            For i = 0 To intRows
                Try
                    cmmKernal.CommandText = dsUpdate.Tables(0).Rows(i)("SQLCOMMAND").ToString
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(dsUpdate.Tables(0).Rows(i)("SQLCOMMAND").ToString) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                Catch
                    Dim iColumn As Integer = 0
                    'Table Name
                    strXMLValue = strXMLValue & "<" & strTableName & ">"
                    For iColumn = 0 To dsUpdate.Tables(0).Columns.Count - 1
                        strXMLValue = strXMLValue & "<" & dsUpdate.Tables(0).Columns(iColumn).ColumnName & ">" _
                            & dsUpdate.Tables(0).Rows(i)(iColumn) _
                            & "</" & dsUpdate.Tables(0).Columns(iColumn).ColumnName & ">"
                    Next
                    strXMLValue = strXMLValue & "</" & strTableName & ">"
                End Try
            Next

            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
            End If

        Catch e1 As Exception
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw

        Finally
            CloseConnection(cnnKernal)
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not oTran Is Nothing Then oTran = Nothing

            If strXMLValue <> "" Then
                '加上<NewDataSet>Tag 以符合DataSet的格式
                strXMLValue = "<NewDataSet>" & strXMLValue & "</NewDataSet>"
                'Combine Return Value
                ExecuteSQLNoneQuery_Upd2 = CombineXMLReturnValue("executesqlnonequeryupd2", strTableName, "DataSet", "", strXMLValue, "")
            End If

        End Try

    End Function

    Public Function ExecuteSQLNoneQuery_Batch(ByRef strDatabaseType As String, ByRef strConnectionString As String, ByRef CollectionSQL As Collection,
        Optional ByVal BatchSerial As String = "", Optional ByVal dtBatchLot As DataTable = Nothing) As String

        Dim objLic As New iMESLicxManager.clsDBCom
        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction
        Dim strCommandText As String
        Dim arrPar As Array
        Dim i As Integer

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//Create connection
            cnnKernal = objLic.CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '取出BatchSerial的Lot.
            If BatchSerial <> "" OrElse (dtBatchLot Is Nothing OrElse dtBatchLot.Rows.Count = 0) Then
                Dim daTemp As OleDb.OleDbDataAdapter
                Dim dsTemp As DataSet
                Dim cmmTemp As OleDb.OleDbCommand
                Dim strSQL As String
                strSQL = "Select LotNo From tblWIPLotState Where BatchSerial='" & BatchSerial & "'"
                cmmTemp = New OleDb.OleDbCommand(strSQL, cnnKernal)
                daTemp = New OleDb.OleDbDataAdapter(cmmTemp)
                dsTemp = New DataSet
                daTemp.Fill(dsTemp, "BatchLot")
                cmmTemp.Dispose()
                daTemp.Dispose()
                dtBatchLot = dsTemp.Tables("BatchLot")
            End If

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            Dim pc As OleDb.OleDbParameterCollection = cmmKernal.Parameters

            If strDatabaseType = "oracle" Then
                For Each strCommandText In CollectionSQL
                    If InStr(strCommandText, "=?", CompareMethod.Text) > 0 OrElse InStr(strCommandText, "?,", CompareMethod.Text) > 0 Then
                        '有帶參數的Command
                        For i = 0 To dtBatchLot.Rows.Count - 1
                            'Parameter
                            pc.Clear()
                            pc.Add("LotNo", System.Data.OleDb.OleDbType.Char)
                            pc("LotNo").Value = dtBatchLot.Rows(i)("LotNo")
                            cmmKernal.CommandText = strCommandText
                            cmmKernal.ExecuteNonQuery()

                            '//TransactionLog
                            '2011/12/16 Aaron, 寫入交易紀錄
                            If blnTransactionLog = True Then
                                intSQLID += 1
                                strCommandText = strCommandText.Replace("?", "'" & dtBatchLot.Rows(i)("LotNo") & "'")
                                cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                                cmmKernal.ExecuteNonQuery()
                            End If

                        Next
                    Else
                        '沒有帶參數的Command
                        pc.Clear()
                        cmmKernal.CommandText = strCommandText
                        cmmKernal.ExecuteNonQuery()

                        '//TransactionLog
                        '2011/12/16 Aaron, 寫入交易紀錄
                        If blnTransactionLog = True Then
                            intSQLID += 1
                            cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                            cmmKernal.ExecuteNonQuery()
                        End If

                    End If
                Next strCommandText
            Else
                For Each strCommandText In CollectionSQL
                    If InStr(strCommandText, "=?", CompareMethod.Text) > 0 OrElse InStr(strCommandText, "?,", CompareMethod.Text) > 0 Then
                        '有帶參數的Command
                        For i = 0 To dtBatchLot.Rows.Count - 1
                            'Parameter
                            pc.Clear()
                            pc.Add("LotNo", System.Data.OleDb.OleDbType.Char)
                            pc("LotNo").Value = dtBatchLot.Rows(i)("LotNo")
                            strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                            cmmKernal.CommandText = strCommandText
                            cmmKernal.ExecuteNonQuery()

                            '//TransactionLog
                            '2011/12/16 Aaron, 寫入交易紀錄
                            If blnTransactionLog = True Then
                                intSQLID += 1
                                strCommandText = strCommandText.Replace("?", "'" & dtBatchLot.Rows(i)("LotNo") & "'")
                                cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                                cmmKernal.ExecuteNonQuery()
                            End If

                        Next
                    Else
                        '沒有帶參數的Command
                        pc.Clear()
                        strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                        cmmKernal.CommandText = strCommandText
                        cmmKernal.ExecuteNonQuery()

                        '//TransactionLog
                        '2011/12/16 Aaron, 寫入交易紀錄
                        If blnTransactionLog = True Then
                            intSQLID += 1
                            cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                            cmmKernal.ExecuteNonQuery()
                        End If

                    End If
                Next strCommandText
            End If

            ' 2014/10/03 YF, DB SERVER語系若不是繁體, 或有其它的ERROR, 則訊息不會被拋出, 也沒有Rollback動作
            'Transaction commit
            oTran.Commit()
            'Dim fromtime As DateTime = Now()
            'Do Until DateDiff(DateInterval.Second, fromtime, Now()) > 20
            '    Try
            '        oTran.Commit()
            '        Exit Do
            '    Catch ex As Exception
            '        'deadlock重新執行(for SQL)
            '        If InStr(ex.Message, "已經被另一個處理序鎖死並造成死結") > 0 Then
            '            oTran.Rollback()
            '        Else
            '            Exit Do
            '        End If
            '    End Try
            'Loop

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            '因Error有可能發生在BeginTransaction之前,故加上If Not oTran Is Nothing的判斷,
            '否則錯誤會被oTran"並未將物件參考設定為物件的執行個體"cover.
            '陈泳澄 此 SqlTransaction 已完成；它再也无法使用。
            Try
                If oTran IsNot Nothing Then oTran.Rollback()
                If strDatabaseType <> "oracle" Then
                    '取出最底層的Error
                    Dim intUBound As Integer
                    intUBound = ex.Errors.Count - 1
                    Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
                Else
                    '取出Oracle的ErrorCode,:前的字元.
                    Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
                End If
            Catch e1 As Exception
                Digiwin.SMES.Helper.Common.funWriteTxtFile("sql异常", "事务执行异常：" + e1.Message)
                Throw New Exception(e1.Message)
            End Try



        Catch e1 As Exception
                '發生問題時所以Transaction roll back
                If oTran IsNot Nothing Then oTran.Rollback()
                Throw
            Finally
                If Not oTran Is Nothing Then oTran = Nothing
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            objLic.CloseConnection(cnnKernal)
            If Not objLic Is Nothing Then objLic = Nothing

        End Try


    End Function

    ' ========================================================================================================================
    '  程式名稱：ExecuteSQLNoneQuery_UPD3
    '  傳入參數：strDatabaseType, strConnectionString, CollectionSQL
    '  傳回參數：
    '  用    途：批次執行 Collection Object 的 SQL Statement
    '  撰寫人員：Owen.Liu
    '  撰寫日期：2008/09/03
    '  備    註：因為 <||> 在 Oracle 是字串連接 運算元， 因此 不能將 <||> 後的字串是為參數
    ' ========================================================================================================================
    Public Function ExecuteSQLNoneQuery_UPD3(ByRef strDatabaseType As String, ByRef strConnectionString As String, ByRef CollectionSQL As Collection) As String
        '//執行DML的SQL Statement

        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction
        Dim MaxIndex As Integer
        Dim CurIndex As Integer
        Dim strCommandText As String

        Dim arrPar As Array

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//Create connection
            cnnKernal = CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            Dim pc As OleDb.OleDbParameterCollection = cmmKernal.Parameters
            Dim Index_i As Integer

            '//Execute
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            For Each strCommandText In CollectionSQL
                strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                cmmKernal.CommandText = strCommandText
                cmmKernal.ExecuteNonQuery()

                '//TransactionLog
                '2011/12/16 Aaron, 寫入交易紀錄
                If blnTransactionLog = True Then
                    intSQLID += 1
                    cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                    cmmKernal.ExecuteNonQuery()
                End If

            Next strCommandText

            'Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            oTran.Rollback()
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.ExecuteSQLNoneQuery_UPD: " & e1.Message & Chr(13) & strCommandText)

        Finally
            CloseConnection(cnnKernal)
            If Not cmmKernal Is Nothing Then
                cmmKernal.Dispose()
            End If
            oTran = Nothing

        End Try


    End Function

    Public Function ExecuteSQLNoneQuery_CLOB_Multi(ByRef strDatabaseType As String, _
                                                   ByRef strConnectionString As String, _
                                                   Optional ByRef CollectionSQL As Collection = Nothing, _
                                                   Optional ByRef arrParameterCommandText As ArrayList = Nothing, _
                                                   Optional ByRef arrParameter As ArrayList = Nothing, _
                                                   Optional ByRef CollectionSQL_Rows As Collection = Nothing) As String

        Dim objLic As New iMESLicxManager.clsDBCom
        Dim cnnKernal As IDbConnection
        Dim cmmKernal As IDbCommand
        Dim param As IDataParameter

        Dim oTran As IDbTransaction
        Dim strCommandText As String
        Dim strSQLAddField As String
        Dim strSQLAddValue As String
        Dim lngAffectRows, lngRowCount As Long
        Dim strParameterCommandText As String

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try

            Dim strConnection As String = objLic.GetConnectionString_LOB(strConnectionString)

            If strDatabaseType = "oracle" Then
                cnnKernal = New OracleClient.OracleConnection(strConnection)
                cmmKernal = New OracleClient.OracleCommand
                cmmKernal.Connection = cnnKernal

                'Begin Transaction
                cnnKernal.Open()
                oTran = cnnKernal.BeginTransaction()
                cmmKernal.Transaction = oTran

            ElseIf strDatabaseType = "mssql" Then
                cnnKernal = New SqlClient.SqlConnection(strConnection)
                cmmKernal = New SqlClient.SqlCommand
                cmmKernal.Connection = cnnKernal

                'Begin Transaction
                cnnKernal.Open()
                oTran = cnnKernal.BeginTransaction()
                cmmKernal.Transaction = oTran

            Else
                Throw New iMESException.MESException("0000-200015", "Database not support,only support Oracle & SQL !")
            End If

            '//Execute
            If Not CollectionSQL Is Nothing AndAlso CollectionSQL.Count > 0 Then

                If Not IsNothing(CollectionSQL_Rows) AndAlso CollectionSQL.Count <> CollectionSQL_Rows.Count Then
                    Throw New iMESException.MESException("0000-203085", "SQLCommand collection(" & CollectionSQL.Count & ") and check affect row collection(" & CollectionSQL_Rows.Count & "),count is not balance!")
                End If

                For i As Integer = 1 To CollectionSQL.Count
                    strCommandText = CollectionSQL(i).ToString
                    strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)

                    If Not IsNothing(CollectionSQL_Rows) Then
                        lngAffectRows = CollectionSQL_Rows(i)
                    Else
                        lngAffectRows = -1
                    End If
                    cmmKernal.CommandText = strCommandText
                    lngRowCount = cmmKernal.ExecuteNonQuery()
                    If (lngAffectRows <> -1) AndAlso lngAffectRows <> lngRowCount Then
                        Throw New iMESException.MESException("0000-200032", "The number of affected rows is not expected. Data Not Coincidence!" & ControlChars.CrLf & _
                                                ", Affect rows:" & lngAffectRows & ", In fact rows:" & lngRowCount & "," & ControlChars.CrLf & strCommandText)
                    End If

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                Next
            End If


            If arrParameterCommandText.Count > 0 AndAlso arrParameter.Count > 0 Then
                For i As Integer = 0 To arrParameterCommandText.Count - 1
                    cmmKernal.Parameters.Clear()
                    strParameterCommandText = SQLCommandTuning(strDatabaseType, arrParameterCommandText(i).ToString)
                    cmmKernal.CommandText = strParameterCommandText

                    Dim c As Collection = arrParameter(i)
                    For j As Integer = 1 To c.Count
                        cmmKernal.Parameters.Add(c.Item(j))
                    Next
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        cmmKernal.Parameters.Clear()

                        intSQLID += 1

                        If strDatabaseType = "oracle" Then

                            cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText,SQLType) Values('" & strTranID & "'," & intSQLID & ",:SQLText,2)"
                            param = New OracleClient.OracleParameter(":SQLText", OracleClient.OracleType.Clob, strParameterCommandText.Length, ParameterDirection.Input)
                            param.Value = strParameterCommandText
                            cmmKernal.Parameters.Add(param)
                            param = Nothing

                        ElseIf strDatabaseType = "mssql" Then

                            cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText,SQLType) Values('" & strTranID & "'," & intSQLID & ",@SQLText,2)"
                            param = New SqlClient.SqlParameter("@SQLText", SqlDbType.NVarChar, -1, ParameterDirection.Input, _
                                                     False, 0, 0, Nothing, DataRowVersion.Current, strParameterCommandText)
                            cmmKernal.Parameters.Add(param)
                            param = Nothing
                        Else
                            Throw New iMESException.MESException("0000-200015", "Database not support,only support Oracle & SQL !")
                        End If

                        cmmKernal.ExecuteNonQuery()

                        cmmKernal.Parameters.Clear()


                        For j As Integer = 1 To c.Count

                            If strDatabaseType = "oracle" Then

                                cmmKernal.CommandText = "Insert Into tblSYSTrans_CLOB(TransID, SQLID, PName, PVarchar) Values('" & strTranID & "'," & intSQLID & ",'" & CType(c.Item(j), OracleClient.OracleParameter).ParameterName.ToString.TrimStart(":", "@") & "',:PVarchar)"
                                param = New OracleClient.OracleParameter(":PVarchar", OracleClient.OracleType.Clob, CType(c.Item(j), OracleClient.OracleParameter).Size)
                                param.Value = CType(c.Item(j), OracleClient.OracleParameter).Value
                                cmmKernal.Parameters.Add(param)
                                param = Nothing

                            ElseIf strDatabaseType = "mssql" Then

                                cmmKernal.CommandText = "Insert Into tblSYSTrans_CLOB(TransID, SQLID, PName, PVarchar) Values('" & strTranID & "'," & intSQLID & ",'" & CType(c.Item(j), SqlClient.SqlParameter).ParameterName.ToString.TrimStart(":", "@") & "',@PVarchar)"
                                param = New SqlClient.SqlParameter("@PVarchar", SqlDbType.NVarChar, CType(c.Item(j), SqlClient.SqlParameter).Size, ParameterDirection.Input)
                                param.Value = CType(c.Item(j), SqlClient.SqlParameter).Value
                                cmmKernal.Parameters.Add(param)
                                param = Nothing

                            Else
                                Throw New iMESException.MESException("0000-200015", "Database not support,only support Oracle & SQL !")
                            End If

                            cmmKernal.ExecuteNonQuery()

                        Next
                    End If
                Next
            End If

            ' 2014/10/03 YF, DB SERVER語系若不是繁體, 或有其它的ERROR, 則訊息不會被拋出, 也沒有Rollback動作
            'Transaction commit
            oTran.Commit()
            'Dim fromtime As DateTime = Now()
            'Do Until DateDiff(DateInterval.Second, fromtime, Now()) > 20
            '    Try
            '        oTran.Commit()
            '        Exit Do
            '    Catch ex As Exception
            '        'deadlock重新執行(for SQL)
            '        If InStr(ex.Message, "已經被另一個處理序鎖死並造成死結") > 0 Then
            '            oTran.Rollback()
            '        Else
            '            Exit Do
            '        End If
            '    End Try
            'Loop

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            '因Error有可能發生在BeginTransaction之前,故加上If Not oTran Is Nothing的判斷,
            '否則錯誤會被oTran"並未將物件參考設定為物件的執行個體"cover.
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message & ControlChars.CrLf & strCommandText)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message & ControlChars.CrLf & strCommandText)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw
        Finally
            If Not oTran Is Nothing Then oTran = Nothing
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not objLic Is Nothing Then objLic = Nothing

            If (Not cmmKernal Is Nothing) Then cmmKernal.Dispose()

            If Not cnnKernal Is Nothing Then
                cnnKernal.Close()
                cnnKernal.Dispose()
            End If

            '    If (Not dr Is Nothing) Then dr.Close()

        End Try

    End Function

    Public Function SQLCommandTuning(ByVal DatabaseType As String, ByRef strSQLCommand As String) As String

        '根據 Server 修正 SQL Statement

        Dim p1 As Integer
        Dim p2 As Integer
        Dim p3 As Integer
        Dim s1 As String
        Dim s2 As String
        Dim newSQL As String

        Try
            If DatabaseType = "oracle" Then       '程式以Oracle語法開發,故不需轉換
                SQLCommandTuning = strSQLCommand
            ElseIf DatabaseType = "mssql" Or DatabaseType = "msaccess" OrElse DatabaseType = "informix" Then            'SQL與Access語法相同
                newSQL = strSQLCommand

                '置換 To_Date
                p1 = InStr(1, UCase(newSQL), "TO_DATE")
                Do While p1 > 0
                    p2 = InStr(p1, newSQL, ",")
                    p3 = InStr(p2 + 1, newSQL, ")")
                    newSQL = Left(newSQL, p1 - 1) & Mid(newSQL, p1 + 8, p2 - p1 - 8) & Right(newSQL, Len(newSQL) - p3)
                    p1 = InStr(1, UCase(newSQL), "TO_DATE")
                Loop

                '置換 To_Char
                p1 = InStr(1, UCase(newSQL), "TO_CHAR")
                Do While p1 > 0
                    p2 = InStr(p1, newSQL, ",")
                    p3 = InStr(p2 + 1, newSQL, ")")
                    newSQL = Left(newSQL, p1 - 1) & Mid(newSQL, p1 + 8, p2 - p1 - 8) & Right(newSQL, Len(newSQL) - p3)
                    p1 = InStr(1, UCase(newSQL), "TO_CHAR")
                Loop

                '置換 Nvl
                p1 = InStr(1, UCase(newSQL), "NVL(")
                If p1 > 0 Then
                    newSQL = UCase(newSQL).Replace("NVL(", "ISNULL(")
                End If

                '置換 Length
                p1 = InStr(1, UCase(newSQL), "LENGTH(")
                If p1 > 0 Then
                    newSQL = UCase(newSQL).Replace("LENGTH(", "LEN(")
                End If

                '置換 SubStr
                p1 = InStr(1, UCase(newSQL), "SUBSTR(")
                If p1 > 0 Then
                    newSQL = UCase(newSQL).Replace("SUBSTR(", "SUBSTRING(")
                End If

                '置換 LineNo 關鍵字
                p1 = InStr(1, UCase(newSQL), "LINENO")
                Do While p1 > 0
                    s1 = Mid(newSQL, p1 - 1, 1)   'LineNo的前一個字元
                    s2 = Mid(newSQL, p1 + 6, 1)   'LineNo的後一個字元
                    If (s1 = "," Or s1 = " " Or s1 = "(") And (s2 = "," Or s2 = " " Or s2 = ")" Or s2 = "") Then
                        newSQL = Left(newSQL, p1 - 1) & "[" & Mid(newSQL, p1, 6) & "]" & Right(newSQL, Len(newSQL) - p1 - 5)
                    End If
                    p1 = InStr(p1 + 6, UCase(newSQL), "LINENO")
                Loop

                'Add by yun 94.6.2
                '置換 Alter Table  Add Column 之 Column Type
                If InStr(1, UCase(newSQL), "ALTER") > 0 And InStr(1, UCase(newSQL), "TABLE") > 0 And InStr(1, UCase(newSQL), "ADD") > 0 Then
                    If InStr(1, UCase(newSQL), "VARCHAR2") > 0 Then
                        newSQL = UCase(newSQL).Replace("VARCHAR2", "VARCHAR")
                    End If
                    If InStr(1, UCase(newSQL), "NUMBER") > 0 Then
                        newSQL = UCase(newSQL).Replace("NUMBER", "DECIMAL")
                    End If

                    '去除ADD (                  )  之括號
                    p1 = InStr(1, UCase(newSQL), "ADD")
                    s1 = Mid(newSQL, p1 + 3)   'ADD 後面的字串

                    If Left(s1.Trim, 1) = "(" And Right(s1.Trim, 1) = ")" Then
                        s1 = Mid(s1, 2, Len(s1 - 1))       '截去前後括號
                        newSQL = Mid(newSQL, 1, p1 + 2) & " " & s1
                    End If
                End If

                strSQLCommand = newSQL
                SQLCommandTuning = newSQL
            End If

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.SQLCommandTuning: " & e1.Message)

        End Try

    End Function

    Public Function ExecuteSQLNoneQuery_UPDCheckRowCount(ByRef strDatabaseType As String, ByRef strConnectionString As String, ByRef CollectionSQL As Collection) As String

        '//執行DML的SQL Statement

        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction
        Dim MaxIndex As Integer
        Dim CurIndex As Integer
        Dim strCommandText As String
        Dim strCommandDesc As String

        Dim arrPar As Array, RowsCount As Integer

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//Create connection
            cnnKernal = CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            Dim pc As OleDb.OleDbParameterCollection = cmmKernal.Parameters
            Dim Index_i As Integer

            '//Execute
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            For Each strCommandText In CollectionSQL
                If InStr(strCommandText, "||") <> 0 Then    '此 SQL 為Parameter Type
                    arrPar = Split(strCommandText, "||")
                    strCommandText = SQLCommandTuning(strDatabaseType, arrPar(0))


                    For Index_i = 1 To arrPar.Length - 1
                        pc.Add("NodeXMLString_NEW" & Index_i, System.Data.OleDb.OleDbType.LongVarChar)
                        pc("NodeXMLString_NEW" & Index_i).Value = arrPar(Index_i)
                    Next Index_i

                    cmmKernal.CommandText = strCommandText
                    RowsCount = cmmKernal.ExecuteNonQuery()

                    strCommandDesc = strCommandText.Trim().ToLower()
                    If ((strCommandDesc.StartsWith("update")) AndAlso (RowsCount = 0)) Then
                        Throw New iMESException.MESException("0000-200032", "SQL update: The number of rows affected is zero. Data Not Coincidence!(" & strCommandText & ")")
                    End If

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                Else
                    strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                    cmmKernal.CommandText = strCommandText
                    RowsCount = cmmKernal.ExecuteNonQuery()

                    strCommandDesc = strCommandText.Trim().ToLower()
                    If ((strCommandDesc.StartsWith("update")) AndAlso (RowsCount = 0)) Then
                        Throw New iMESException.MESException("0000-200032", "SQL update: The number of rows affected is zero. Data Not Coincidence!(" & strCommandText & ")")
                    End If

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                End If

            Next strCommandText

            'Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw
        Finally
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not oTran Is Nothing Then oTran = Nothing
            CloseConnection(cnnKernal)

        End Try


    End Function

    '功能:執行command及資料一致性檢查
    '傳入值:
    '傳回值:success(成功), fail(失敗)
    '撰寫:2008/07/17,sammi.
    Public Function ExecuteSQLNoneQuery_ChkRowCount(ByRef strDatabaseType As String, ByRef strConnectionString As String, _
        ByRef CollectionSQL As Collection, ByRef CollectionSQL_Rows As Collection) As String

        'CollectionSQL & CollectionSQL_Rows,並須是對應存在的.若不想檢查AffectRows可在對應的Index上,將值定為-1.

        Dim objLic As New iMESLicxManager.clsDBCom
        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            If CollectionSQL.Count <> CollectionSQL_Rows.Count Then
                Throw New iMESException.MESException("0000-203085", "SQLCommand collection(" & CollectionSQL.Count & ") and check affect row collection(" & CollectionSQL_Rows.Count & "),count is not balance!")
            End If

            '//Create connection
            cnnKernal = objLic.CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            '//Execute
            Dim strKey, strCommandText As String
            Dim lngAffectRows, lngRowCount As Long
            Dim i As Integer

            For i = 1 To CollectionSQL.Count
                strCommandText = CollectionSQL(i).ToString
                strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                lngAffectRows = CollectionSQL_Rows(i)
                cmmKernal.CommandText = strCommandText
                lngRowCount = cmmKernal.ExecuteNonQuery()
                If (lngAffectRows <> -1) AndAlso lngAffectRows <> lngRowCount Then
                    Throw New iMESException.MESException("0000-200032", "The number of affected rows is not expected. Data Not Coincidence!" & ControlChars.CrLf & _
                                            ", Affect rows:" & lngAffectRows & ", In fact rows:" & lngRowCount & "," & ControlChars.CrLf & strCommandText)
                End If

                '//TransactionLog
                '2011/12/16 Aaron, 寫入交易紀錄
                If blnTransactionLog = True Then
                    intSQLID += 1
                    cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                    cmmKernal.ExecuteNonQuery()
                End If

            Next

            'Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw

        Finally
            If Not oTran Is Nothing Then oTran = Nothing
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            objLic.CloseConnection(cnnKernal)
            If Not objLic Is Nothing Then objLic = Nothing

        End Try

    End Function


    Public Function ExecuteODBCSQLNoneQuery(ByRef strDatabaseType As String, ByRef strERPConnectionString As String, ByRef strCommandText As String) As String

        '//執行DML的SQL Statement
        Dim cnnKernal As Odbc.OdbcConnection
        Dim cmmKernal As Odbc.OdbcCommand
        Dim oTran As Odbc.OdbcTransaction

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)

            '//Create connection
            cnnKernal = CreateODBCConnection(strERPConnectionString)

            '//將connection Assign給Command
            cmmKernal = New Odbc.OdbcCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            '//Execute
            cmmKernal.CommandText = strCommandText
            cmmKernal.ExecuteNonQuery()

            '//TransactionLog
            '2011/12/16 Aaron, 寫入交易紀錄
            If blnTransactionLog = True Then
                intSQLID += 1
                cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                cmmKernal.ExecuteNonQuery()
            End If

            ''Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            '取出最底層的Error
            Dim intUBound As Integer
            intUBound = ex.Errors.Count - 1
            Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw 'New Exception("modTC.ExecuteSQLNoneQuery: " & e1.Message & Chr(13) & strCommandText)

        Finally
            CloseODBCConnection(cnnKernal)
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not oTran Is Nothing Then oTran = Nothing

        End Try

    End Function

    Public Function ExecuteODBCSQLNoneQuery_UPD(ByRef strDatabaseType As String, ByRef strERPConnectionString As String, ByRef CollectionSQL As Collection) As String
        '//執行DML的SQL Statement

        Dim cnnKernal As Odbc.OdbcConnection
        Dim cmmKernal As Odbc.OdbcCommand
        Dim oTran As Odbc.OdbcTransaction
        Dim MaxIndex As Integer
        Dim CurIndex As Integer
        Dim strCommandText As String

        Dim arrPar As Array

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//Create connection
            cnnKernal = CreateODBCConnection(strERPConnectionString)

            '//將connection Assign給Command
            cmmKernal = New Odbc.OdbcCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            Dim pc As Odbc.OdbcParameterCollection = cmmKernal.Parameters
            Dim Index_i As Integer

            '//Execute
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            For Each strCommandText In CollectionSQL
                If InStr(strCommandText, "||") <> 0 Then    '此 SQL 為Parameter Type
                    arrPar = Split(strCommandText, "||")
                    strCommandText = SQLCommandTuning(strDatabaseType, arrPar(0))

                    For Index_i = 1 To arrPar.Length - 1
                        pc.Add("NodeXMLString_NEW" & Index_i, System.Data.OleDb.OleDbType.LongVarChar)
                        pc("NodeXMLString_NEW" & Index_i).Value = arrPar(Index_i)
                    Next Index_i

                    cmmKernal.CommandText = strCommandText
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                Else
                    strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                    cmmKernal.CommandText = strCommandText
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                End If

            Next strCommandText

            'Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()

            '取出最底層的Error
            Dim intUBound As Integer
            intUBound = ex.Errors.Count - 1
            Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.ExecuteSQLNoneQuery_UPD: " & e1.Message & Chr(13) & strCommandText)

        Finally
            CloseODBCConnection(cnnKernal)
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not oTran Is Nothing Then oTran = Nothing

        End Try


    End Function

    Public Function ExecuteSQLNoneQuery_ReturnRowCount(ByRef strDatabaseType As String, _
ByRef strConnectionString As String, ByRef strCommandText As String) As Integer
        '//執行DML的SQL Statement

        Dim objLic As New iMESLicxManager.clsDBCom

        Dim cnnKernal As OleDb.OleDbConnection
        Dim cmmKernal As OleDb.OleDbCommand
        Dim oTran As OleDb.OleDbTransaction

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)

            '//Create connection
            cnnKernal = objLic.CreateConnection(strConnectionString)

            '//將connection Assign給Command
            cmmKernal = New OleDb.OleDbCommand
            cmmKernal.Connection = cnnKernal

            '//Begin Transaction
            oTran = cnnKernal.BeginTransaction()
            cmmKernal.Transaction = oTran

            '//Execute
            cmmKernal.CommandText = strCommandText
            ExecuteSQLNoneQuery_ReturnRowCount = cmmKernal.ExecuteNonQuery()

            '//TransactionLog
            '2011/12/16 Aaron, 寫入交易紀錄
            If blnTransactionLog = True Then
                intSQLID += 1
                cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                cmmKernal.ExecuteNonQuery()
            End If

            ''Transaction commit
            oTran.Commit()

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            '因Error有可能發生在BeginTransaction之前,故加上If Not oTran Is Nothing的判斷,
            '否則錯誤會被oTran"並未將物件參考設定為物件的執行個體"cover.
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw
        Finally
            If Not oTran Is Nothing Then oTran = Nothing
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            objLic.CloseConnection(cnnKernal)
            If Not objLic Is Nothing Then objLic = Nothing

        End Try

    End Function


    Public Function ExecuteSQLNoneQuery_AttachFile(ByRef strDatabaseType As String, _
                                                   ByRef strConnectionString As String, _
                                                   Optional ByRef CollectionSQL As Collection = Nothing, _
                                                   Optional ByRef strParameterCommandText As String = defString, _
                                                   Optional ByRef CollectionParameter As Collection = Nothing) As String

        Dim objLic As New iMESLicxManager.clsDBCom
        Dim cnnKernal As IDbConnection
        Dim cmmKernal As IDbCommand
        Dim param As IDataParameter

        Dim oTran As IDbTransaction
        Dim strCommandText As String
        Dim strSQLAddField As String
        Dim strSQLAddValue As String

        '2011/12/16 Aaron, 新增交易紀錄變數
        Dim strTranID As String = System.Guid.NewGuid.ToString()
        Dim intSQLID As Integer = 0
        Dim blnTransactionLog As Boolean = False

        '//TransactionLog
        Try
            If Not String.IsNullOrEmpty(System.Configuration.ConfigurationManager.AppSettings("TransactionLog")) AndAlso System.Configuration.ConfigurationManager.AppSettings("TransactionLog").ToUpper.Trim = "TRUE" Then
                blnTransactionLog = True
            Else
                blnTransactionLog = False
            End If
        Catch ex As Exception
            blnTransactionLog = False
        End Try

        Try

            Dim strConnection As String = objLic.GetConnectionString_LOB(strConnectionString)

            If strDatabaseType = "oracle" Then
                cnnKernal = New OracleClient.OracleConnection(strConnection)
                cmmKernal = New OracleClient.OracleCommand
                cmmKernal.Connection = cnnKernal

                'Begin Transaction
                cnnKernal.Open()
                oTran = cnnKernal.BeginTransaction()
                cmmKernal.Transaction = oTran

            ElseIf strDatabaseType = "mssql" Then
                cnnKernal = New SqlClient.SqlConnection(strConnection)
                cmmKernal = New SqlClient.SqlCommand
                cmmKernal.Connection = cnnKernal

                'Begin Transaction
                cnnKernal.Open()
                oTran = cnnKernal.BeginTransaction()
                cmmKernal.Transaction = oTran

            Else
                Throw New iMESException.MESException("0000-200015", "Database not support,only support Oracle & SQL !")
            End If

            '//Execute
            '//檢查DataBase Type,並依DataBase Type將語法轉換  
            If Not CollectionSQL Is Nothing AndAlso CollectionSQL.Count > 0 Then
                For Each strCommandText In CollectionSQL

                    strCommandText = SQLCommandTuning(strDatabaseType, strCommandText)
                    cmmKernal.CommandText = strCommandText
                    cmmKernal.ExecuteNonQuery()

                    '//TransactionLog
                    '2011/12/16 Aaron, 寫入交易紀錄
                    If blnTransactionLog = True Then
                        intSQLID += 1
                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText) Values('" & strTranID & "'," & intSQLID & ",'" & ChkSingleQuation(strCommandText) & "')"
                        cmmKernal.ExecuteNonQuery()
                    End If

                Next strCommandText
            End If


            If strParameterCommandText <> defString Then

                cmmKernal.Parameters.Clear()
                strParameterCommandText = SQLCommandTuning(strDatabaseType, strParameterCommandText)
                cmmKernal.CommandText = strParameterCommandText

                For i As Integer = 1 To CollectionParameter.Count
                    cmmKernal.Parameters.Add(CollectionParameter.Item(i))
                Next

                cmmKernal.ExecuteNonQuery()

                '//TransactionLog
                '2011/12/16 Aaron, 寫入交易紀錄
                If blnTransactionLog = True Then
                    cmmKernal.Parameters.Clear()

                    intSQLID += 1

                    If strDatabaseType = "oracle" Then

                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText,SQLType) Values('" & strTranID & "'," & intSQLID & ",:SQLText,1)"
                        param = New OracleClient.OracleParameter(":SQLText", OracleClient.OracleType.Clob, strParameterCommandText.Length, ParameterDirection.Input)
                        param.Value = strParameterCommandText
                        cmmKernal.Parameters.Add(param)
                        param = Nothing

                    ElseIf strDatabaseType = "mssql" Then

                        cmmKernal.CommandText = "Insert Into tblSYSTrans(TransID, SQLID, SQLText,SQLType) Values('" & strTranID & "'," & intSQLID & ",@SQLText,1)"
                        param = New SqlClient.SqlParameter("@SQLText", SqlDbType.NVarChar, -1, ParameterDirection.Input, _
                                                 False, 0, 0, Nothing, DataRowVersion.Current, strParameterCommandText)
                        cmmKernal.Parameters.Add(param)
                        param = Nothing
                    Else
                        Throw New iMESException.MESException("0000-200015", "Database not support,only support Oracle & SQL !")
                    End If

                    cmmKernal.ExecuteNonQuery()

                    cmmKernal.Parameters.Clear()


                    For i As Integer = 1 To CollectionParameter.Count

                        If strDatabaseType = "oracle" Then

                            cmmKernal.CommandText = "Insert Into tblSYSTrans_AttachFile(TransID, SQLID, PName, PBinary) Values('" & strTranID & "'," & intSQLID & ",'" & CType(CollectionParameter.Item(i), OracleClient.OracleParameter).ParameterName.ToString.TrimStart(":", "@") & "',:PBinary)"
                            param = New OracleClient.OracleParameter(":PBinary", OracleClient.OracleType.Blob, CType(CollectionParameter.Item(i), OracleClient.OracleParameter).Size)
                            param.Value = CType(CollectionParameter.Item(i), OracleClient.OracleParameter).Value
                            cmmKernal.Parameters.Add(param)
                            param = Nothing

                        ElseIf strDatabaseType = "mssql" Then

                            cmmKernal.CommandText = "Insert Into tblSYSTrans_AttachFile(TransID, SQLID, PName, PBinary) Values('" & strTranID & "'," & intSQLID & ",'" & CType(CollectionParameter.Item(i), SqlClient.SqlParameter).ParameterName.ToString.TrimStart(":", "@") & "',@PBinary)"
                            param = New SqlClient.SqlParameter("@PBinary", SqlDbType.VarBinary, CType(CollectionParameter.Item(i), SqlClient.SqlParameter).Size, ParameterDirection.Input)
                            param.Value = CType(CollectionParameter.Item(i), SqlClient.SqlParameter).Value
                            cmmKernal.Parameters.Add(param)
                            param = Nothing

                        Else
                            Throw New iMESException.MESException("0000-200015", "Database not support,only support Oracle & SQL !")
                        End If

                        cmmKernal.ExecuteNonQuery()

                    Next

                End If

            End If

            ' 2014/10/03 YF, DB SERVER語系若不是繁體, 或有其它的ERROR, 則訊息不會被拋出, 也沒有Rollback動作
            'Transaction commit
            oTran.Commit()
            'Dim fromtime As DateTime = Now()
            'Do Until DateDiff(DateInterval.Second, fromtime, Now()) > 20
            '    Try
            '        oTran.Commit()
            '        Exit Do
            '    Catch ex As Exception
            '        'deadlock重新執行(for SQL)
            '        If InStr(ex.Message, "已經被另一個處理序鎖死並造成死結") > 0 Then
            '            oTran.Rollback()
            '        Else
            '            Exit Do
            '        End If
            '    End Try
            'Loop

        Catch ex As OleDb.OleDbException
            '發生問題時所以Transaction roll back
            '因Error有可能發生在BeginTransaction之前,故加上If Not oTran Is Nothing的判斷,
            '否則錯誤會被oTran"並未將物件參考設定為物件的執行個體"cover.
            If Not oTran Is Nothing Then oTran.Rollback()

            If strDatabaseType <> "oracle" Then
                '取出最底層的Error
                Dim intUBound As Integer
                intUBound = ex.Errors.Count - 1
                Throw New iMESException.MESException(ex.Errors.Item(intUBound).NativeError, ex.Errors.Item(intUBound).Message & ControlChars.CrLf & strCommandText)
            Else
                '取出Oracle的ErrorCode,:前的字元.
                Throw New iMESException.MESException(Mid(ex.Message, 1, InStr(ex.Message, ":", CompareMethod.Text) - 1), ex.Message & ControlChars.CrLf & strCommandText)
            End If

        Catch e1 As Exception
            '發生問題時所以Transaction roll back
            If Not oTran Is Nothing Then oTran.Rollback()
            Throw
        Finally
            If Not oTran Is Nothing Then oTran = Nothing
            If Not cmmKernal Is Nothing Then cmmKernal.Dispose()
            If Not objLic Is Nothing Then objLic = Nothing

            If (Not cmmKernal Is Nothing) Then cmmKernal.Dispose()

            If Not cnnKernal Is Nothing Then
                cnnKernal.Close()
                cnnKernal.Dispose()
            End If

            '    If (Not dr Is Nothing) Then dr.Close()



        End Try

    End Function
#End Region

#Region "--- Combine Xml ---"

    Public Function CombineXMLReturnValue(ByRef value_name As String, ByRef name As String, ByRef type As String, _
        ByRef schema As String, ByRef value As String, ByRef desc As String) As String

        Try
            CombineXMLReturnValue = "<" & value_name & ">" & _
                            "<name>" & name & "</name>" & _
                            "<type>" & type & "</type>" & _
                            "<schema>" & schema & "</schema>" & _
                            "<value>" & value & "</value>" & _
                            "<desc>" & desc & "</desc>" & _
                            "</" & value_name & ">"

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.CombineXMLReturnValue: " & e1.Message)

        End Try

    End Function

    Public Function FormatXMLSchema(ByRef XmlSchema As String) As String

        Dim intTruncateLength As Integer

        Try
            FormatXMLSchema = ""

            '將Schema字串的前面一行<?...........?>刪除，以符合XML格式
            intTruncateLength = InStr(XmlSchema, "?>") + 1
            If intTruncateLength <> 1 Then
                FormatXMLSchema = Right(XmlSchema, Len(XmlSchema) - intTruncateLength)
            Else
                FormatXMLSchema = XmlSchema
            End If

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.FormatXMLSchema: " & e1.Message)

        End Try

    End Function

    Public Function CInput(ByRef strInput As String) As String
        '將傳入值內的單引號轉換為可存入資料庫的格式
        '將傳入值內的 &, >, < 三個特殊字元轉換為XmlDocument可解譯之代替符號                  
        '傳入值: strInput包含特殊字元的字串
        '傳回值: 將特殊字元變更為代替符號的字串

        '轉換 ' 為 '' (單引號轉為兩個單引號)
        CInput = Replace(strInput, "'", "''")

        '轉換 & 為 &amp;
        CInput = Replace(CInput, "&", "&amp;")

        'CInput = Replace(CInput, """", "''")   'AddFlow的Xml字串不可將雙引號轉為兩個單引號,XMLToFlow會Error

        '轉換 > 為 &gt;
        CInput = Replace(CInput, ">", "&gt;")

        '轉換 < 為 &lt;
        CInput = Replace(CInput, "<", "&lt;")

    End Function

    Public Function CombineAddXML_Condition(ByVal condition As String) As String

        '2007/03/22,sammi.呼叫CombineAddXML_Condition時,不可加CInput.
        '因CInput會將單引號轉成二個單引號,若Condition為下述範例時,會Error.
        'strAdditional = CombineXMLAdditional(CombineAddXML_Condition("ModuleType in ('REWORK','SLR')"))
        'strParameter += strAdditional

        CombineAddXML_Condition = "<condition>" & condition & "</condition>"

    End Function

#End Region

#Region "--- Separate Additional Xml ---"

    Public Function SeparateAddXML_Add(ByRef strField As String, ByRef strValue As String, ByRef AdditionalXml As String) As String

        '適用Add

        Dim xmlDoc As New Xml.XmlDocument
        Dim xmlNodeList As Xml.XmlNodeList
        Dim i As Integer

        Try
            '//解譯AdditionXml中所新增的欄位
            xmlDoc.LoadXml(AdditionalXml)
            xmlNodeList = xmlDoc.GetElementsByTagName("additional").Item(0).SelectNodes("field")

            '//加入AdditionalXml所新增的欄位
            For i = 0 To xmlNodeList.Count - 1
                'Field
                strField = strField & ", " & xmlNodeList(i).SelectNodes("name").Item(0).InnerText
                'Value
                If UCase(xmlNodeList(i).SelectNodes("type").Item(0).InnerXml) = "DATETIME" Then
                    strValue = strValue & ", To_Date('" & Format(CDate(xmlNodeList(i).SelectNodes("value").Item(0).InnerText), "yyyy/MM/dd H:mm:ss") & "', 'YYYY/MM/DD HH24:MI:SS')"
                ElseIf UCase(xmlNodeList(i).SelectNodes("type").Item(0).InnerXml) = "STRING" Then
                    strValue = strValue & ", '" & xmlNodeList(i).SelectNodes("value").Item(0).InnerText & "'"
                Else
                    strValue = strValue & ", " & xmlNodeList(i).SelectNodes("value").Item(0).InnerText & ""
                End If
            Next i

            '//加上Insert欄位的右括號
            strField = strField & ")"
            '//加上Value的右括號
            strValue = strValue & ")"

            SeparateAddXML_Add = strField & strValue

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw

        End Try

    End Function

    Public Function SeparateAddXML_Edit(ByRef AdditionalXml As String) As String

        Dim xmlDoc As New Xml.XmlDocument
        Dim xmlNodeList As Xml.XmlNodeList
        Dim i As Integer

        '適用於Edit

        Try
            SeparateAddXML_Edit = ""

            '解譯AdditionXml中所新增的欄位
            xmlDoc.LoadXml(AdditionalXml)
            xmlNodeList = xmlDoc.GetElementsByTagName("additional").Item(0).SelectNodes("field")

            '加入AdditionalXml所新增的欄位
            For i = 0 To xmlNodeList.Count - 1
                'Field
                SeparateAddXML_Edit = SeparateAddXML_Edit & xmlNodeList(i).SelectNodes("name").Item(0).InnerText & "="
                'Value
                If UCase(xmlNodeList(i).SelectNodes("type").Item(0).InnerXml) = "DATETIME" Then
                    SeparateAddXML_Edit = SeparateAddXML_Edit & "To_Date('" & Format(CDate(xmlNodeList(i).SelectNodes("value").Item(0).InnerText), "yyyy/MM/dd H:mm:ss") & "', 'YYYY/MM/DD HH24:MI:SS'),"
                ElseIf UCase(xmlNodeList(i).SelectNodes("type").Item(0).InnerXml) = "STRING" Then
                    SeparateAddXML_Edit = SeparateAddXML_Edit & "'" & xmlNodeList(i).SelectNodes("value").Item(0).InnerText & "',"
                Else
                    SeparateAddXML_Edit = SeparateAddXML_Edit & "" & xmlNodeList(i).SelectNodes("value").Item(0).InnerText & ","
                End If
            Next i

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw

        End Try

    End Function

    Public Function SeparateAddXML_Condition(ByRef AdditionalXml As String) As String

        Dim xmlDoc As New Xml.XmlDocument
        Dim xmlNodeList As Xml.XmlNodeList
        Dim i As Integer

        Try
            SeparateAddXML_Condition = ""

            '解譯AdditionXml中所新增的欄位
            xmlDoc.LoadXml(AdditionalXml)
            xmlNodeList = xmlDoc.GetElementsByTagName("additional").Item(0).SelectNodes("condition")

            '加入AdditionalXml所新增的欄位
            For i = 0 To xmlNodeList.Count - 1
                'Condition
                SeparateAddXML_Condition = SeparateAddXML_Condition & " And " & xmlNodeList(i).InnerText
            Next i

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.SeparateAddXML_Condition: " & e1.Message)

        End Try

    End Function

    Public Function SeparateAddXML_Field(ByRef AdditionalXml As String) As String

        Dim xmlDoc As New Xml.XmlDocument
        Dim xmlNodeList As Xml.XmlNodeList
        Dim i As Integer

        Try
            SeparateAddXML_Field = ""

            '解譯AdditionXml中所新增的欄位
            xmlDoc.LoadXml(AdditionalXml)
            xmlNodeList = xmlDoc.GetElementsByTagName("additional").Item(0).SelectNodes("field")

            '加入AdditionalXml所新增的欄位
            For i = 0 To xmlNodeList.Count - 1
                'Field
                SeparateAddXML_Field = SeparateAddXML_Field & ", " & xmlNodeList(i).SelectNodes("name").Item(0).InnerText
            Next i

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.SeparateAddXML_Field: " & e1.Message)

        End Try

    End Function

    Public Function CombineXMLAdditional(ByRef strAdditional As String) As String
        CombineXMLAdditional = "<additional>" & strAdditional
        CombineXMLAdditional = CombineXMLAdditional & "</additional>"
    End Function
#End Region

#Region "--- Others ---"

    Public Function ChkSingleQuation(ByVal SingleString As String) As String

        '//將包含單引號的字串轉為可存入的字串

        Dim i As Integer
        Dim j As Integer
        Dim tmpLeft As String
        Dim tmpRight As String
        Dim tmpResult As String

        Try
            If SingleString = "" Then
                ChkSingleQuation = SingleString
            Else
                If InStr(1, SingleString, "'") <> 0 Then
                    j = 0
                    tmpResult = SingleString

                    For i = 1 To Len(SingleString)
                        If Mid(SingleString, i, 1) = "'" Then
                            tmpLeft = Mid(tmpResult, 1, i + j)
                            tmpRight = Mid(tmpResult, i + j + 1, Len(tmpResult) - i - j)
                            tmpResult = tmpLeft & "'" & tmpRight
                            j = j + 1
                        End If
                    Next i
                    ChkSingleQuation = tmpResult
                Else
                    ChkSingleQuation = SingleString
                End If
            End If

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.ChkSingleQuation: " & e1.Message)

        End Try

    End Function

    Public Function IsBoolean(ByRef strBoolean As String, Optional ByRef strBooleanValue As String = defString) As Boolean
        '此 Function 檢查傳入值是否為Boolean值
        '傳入值: strBoolean欲檢查是否為Boolean值的字串
        '             strBooleanValue將檢查值轉換為True或False的統一字串
        '傳回值: True是Boolean值 ; False不是Boolean值

        IsBoolean = False

        Try
            If strBoolean = "" Then
                IsBoolean = False
            Else
                If Trim(UCase(strBoolean)) <> "Y" And Trim(UCase(strBoolean)) <> "N" _
                        And Trim(UCase(strBoolean)) <> "T" And Trim(UCase(strBoolean)) <> "F" _
                        And Trim(UCase(strBoolean)) <> "YES" And Trim(UCase(strBoolean)) <> "NO" _
                        And Trim(UCase(strBoolean)) <> "TRUE" And Trim(UCase(strBoolean)) <> "FALSE" _
                        And Trim(UCase(strBoolean)) <> "是" And Trim(UCase(strBoolean)) <> "否" _
                        And Trim(UCase(strBoolean)) <> "ON" And Trim(UCase(strBoolean)) <> "OFF" Then

                    IsBoolean = False

                Else
                    If Trim(UCase(strBoolean)) = "Y" Or Trim(UCase(strBoolean)) = "T" Or _
                        Trim(UCase(strBoolean)) = "YES" Or Trim(UCase(strBoolean)) = "TRUE" Or _
                        Trim(UCase(strBoolean)) = "是" Or Trim(UCase(strBoolean)) = "ON" Then
                        IsBoolean = True
                        strBooleanValue = "True"
                    Else
                        IsBoolean = True
                        strBooleanValue = "False"
                    End If
                End If
            End If

        Catch e1 As Exception
            '發生錯誤時丟回本身的Function Name及系統錯誤訊息
            Throw 'New Exception("modTC.IsBoolean: " & e1.Message)

        End Try

    End Function

    '依指定Byte傳回字串
    Public Function GetStringInByteLimit(ByVal OriginalString As String, ByVal ByteLimit As Integer) As String

        Dim inti As Integer
        Dim strTemp As String

        Try
            If System.Text.Encoding.UTF8.GetBytes(OriginalString).Length > ByteLimit Then

                inti = OriginalString.Length
                strTemp = OriginalString.Substring(0, inti)

                Do Until System.Text.Encoding.UTF8.GetBytes(strTemp).Length <= ByteLimit Or inti = 0
                    inti = inti - 1
                    strTemp = OriginalString.Substring(0, inti)
                Loop

                GetStringInByteLimit = strTemp

            Else
                GetStringInByteLimit = OriginalString
            End If

        Catch ex As Exception

            GetStringInByteLimit = OriginalString

        End Try

    End Function

    Public Function CombineAddXML_Add(ByRef name As String, ByRef type As String, ByRef value As String) As String
        If IsNothing(value) Then
            value = ""
        End If
        CombineAddXML_Add = "<field>" & _
                            "<name>" & name & "</name>" & _
                            "<type>" & type & "</type>" & _
                            "<value>" & value.Trim & "</value>" & _
                            "</field>"
    End Function

    Public Function CombineAddXML_Edit(ByRef name As String, ByRef type As String, ByRef value As String) As String
        If IsNothing(value) Then
            value = ""
        End If
        CombineAddXML_Edit = "<field>" &
                            "<name>" & name & "</name>" &
                            "<type>" & type & "</type>" &
                            "<value>" & value.Trim & "</value>" &
                            "</field>"
    End Function

#End Region


    Public Function iMES_Format_Date(ByVal ObjDate As DateTime, Optional ByVal DataBaseType As String = defString) As String
        ' 2012/06/26,EVEN
        '1. 將ObjDate的資料型態改為DateTime
        '2. 改為傳入變數ObjDate

        iMES_Format_Date = String.Empty

        If DataBaseType = defString Then DataBaseType = "oracle"

        '統一轉換日期格式
        '為了讓FormatDateTime轉出來的格式適合不同資料庫，統一呼叫這來轉換
        '之後可以改寫成設定Config的方式來決定資料庫日期格式，或是直接下SQLCommand的方式取出資料庫日期格式
        Dim strFormatString As String = String.Empty
        strFormatString = "yyyy/MM/dd HH:mm:ss"

        '20120209-Larry-之後改寫成依不同的資料庫，用下命令的方式來取出其資料庫的日期格式
        '目前先用一致
        If DataBaseType <> "oracle" Then
            iMES_Format_Date = Format(ObjDate, strFormatString)
        Else
            iMES_Format_Date = Format(ObjDate, strFormatString)
        End If

    End Function


    'EAI標準日期格式轉換String to DateTime
    Public Function CDate_EAI(ByVal Value As String) As DateTime
        Dim strDate As String = ""
        Dim datDate As DateTime = defDateTime

        Try
            If Value.Length < 8 Then Exit Try
            '傳入YYYYMMDD,傳出YYYY/MM/DD
            strDate = Value.Substring(0, 4) & "/" & Value.Substring(4, 2) & "/" & Value.Substring(6, 2)
            If Value.Length < 14 Then Exit Try
            '傳入YYYYMMDDHHMMSS,傳出YYYY/MM/DD HH:MM:SS
            strDate = strDate & " " & Value.Substring(8, 2) & ":" & Value.Substring(10, 2) & ":" & Value.Substring(12, 2)
            '傳入YYYYMMDDHHMMSSFFF,傳出YYYY/MM/DD HH:MM:SS.fff
            If Value.Length < 17 Then Exit Try
            strDate = strDate & "." & Value.Substring(14, 3)
        Catch ex As Exception
            Throw
        Finally
            If strDate <> "" Then datDate = CDate(strDate)
            CDate_EAI = strDate
        End Try


    End Function

End Module
